package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.gdms.dao.ScoreSelectStudao;
import com.zr.gdms.model.Student;
import com.zr.gdms.util.JDBCUtil;

public class ScoreSelectStudaoimpl implements ScoreSelectStudao{
	//根据成绩查询单个学生信息
		@Override
		public List<Student> getScoreStudent(int sid, int score3, int page, int rows) {
			// TODO Auto-generated method stub
			List<Student> st1=new ArrayList<Student>();
			StringBuffer sq2=new StringBuffer();
			sq2.append(" SELECT student.sid,student.sname,student.sage,student.ssex,student.sdepart,student.stel,student.sstate");
			sq2.append(" FROM student");
			sq2.append(" INNER JOIN score");
			sq2.append(" ON student.sid=score.sid");
			sq2.append(" WHERE score.score=? limit ?,?");
			Connection con=JDBCUtil.getConnection();
			try {
				PreparedStatement ps=con.prepareStatement(sq2.toString());
				ps.setInt(1, score3);			
				ps.setInt(2, (page-1)*rows);//起始位置（页码-1)*条数
				ps.setInt(3, rows);//每页条数
				ResultSet rs=ps.executeQuery();
				while(rs.next()){
					Student ss=new Student();
					ss.setSid(rs.getInt("sid"));
					ss.setSname(rs.getString("sname"));
					ss.setSage(rs.getString("sage"));			
					ss.setSsex(rs.getString("ssex"));
					ss.setSdepart(rs.getString("sdepart"));
					ss.setStel(rs.getString("stel"));		
					ss.setSstate(rs.getString("sstate"));			
					st1.add(ss);		
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return st1;	
		}
	//对成绩表学生数求和
		@Override
		public int getStudentsCount(int sid) {
			// TODO Auto-generated method stub
			int count=0;
			StringBuffer sq1=new StringBuffer();
			sq1.append(" SELECT COUNT(sid) AS scount");
			sq1.append(" from score");	
			Connection con=JDBCUtil.getConnection();
			try {
				PreparedStatement pst=con.prepareStatement(sq1.toString());
				ResultSet rt=pst.executeQuery();
				if(rt.next()){
					count=rt.getInt("scount");	
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			return count;
		}
}
